import json
from services.chroma_service import ChromaService
import pymysql
from flask import jsonify
from services.indicator_agent_service import IndicatorAgentService
from services.permission_control import get_authorized_dimensions_and_filters
from services.database_service import DatabaseService

class IndicatorRoutesService:
    def __init__(self):
        self.chroma = ChromaService()
        self.indicator_service = IndicatorAgentService()

        self.meta_db_config = {
            'db_type': 'openGauss',
            'host': '140.143.130.126',
            'port': 40008,
            'user': 'dmp_dev',
            'password': 'dmp@1234',
            'database': 'dmp_dev_1'
        }
    def handle_indicator_query(self,data):
        print(f"收到请求数据: {data}")
        
        query_params = {}  # 默认空，确保返回时有这个字段
        try:
            if not data:
                return jsonify({'status': 'error', 'message': '请求体不能为空'}), 400
            if 'indicators' not in data or 'startDate' not in data or 'endDate' not in data:
                return jsonify({'status': 'error', 'message': '缺少必要的查询参数'}), 400

            indicators = data.get('indicators', [])  # 指标
            dimensions = data.get('dimensions', [])  # 维度
            startDate = data.get('startDate')  # 开始日期
            endDate = data.get('endDate')  # 结束日期
            indicator_desc = data.get('indicator_desc', [indicator for indicator in indicators])
            dimension_desc = data.get('dimension_desc', dimensions)
            granularity = data.get('granularity')  # 年度/季度/月度
            TQindicators = data.get('TQindicators', [])  # 同期
            SQindicators = data.get('SQindicators', [])  # 上期
            filter_value = data.get('filter', '').strip()
            permission_check = data.get('permission_check', False)
            user_id = data.get('user_id', '')

            # 构造过滤表达式
            filter_expr = ''
            if filter_value:
                if dimensions and isinstance(dimensions, list) and len(dimensions) > 0:
                    dimension = dimensions[0]['code']
                    print(f"原始过滤值: '{filter_value}'，使用维度: {dimension}")
                    filter_expr = f"{dimension}->desc=({filter_value})"
                else:
                    print("未找到对应维度，无法构造过滤条件")
            else:
                print("没有传入过滤条件 filter")

            print(f"收到的参数: indicator={indicators}, indicator_desc={indicator_desc}, "
                f"dimension={dimensions}, dimension_desc={dimension_desc}, "
                f"startDate={startDate}, endDate={endDate}, granularity={granularity}, filter={filter_expr}")
            if not indicators:
                return jsonify({'status': 'error', 'message': '指标不能为空'}), 400
            #格式化每个指标，确保描述唯一
            formatted_indicators = []
            for idx, indicator in enumerate(indicators):
                formatted_indicator = f"{indicator} as {indicator_desc[idx]}"
                formatted_indicators.append(formatted_indicator)
            indicator_desc_map = dict(zip(indicators, indicator_desc))
            #格式化同期指标
            formatted_TQindicators = []
            if TQindicators:
                formatted_TQindicators = [
                    f"{indicator} as {indicator_desc_map.get(indicator, indicator)}_同期"
                    for indicator in TQindicators
                ]
            #格式化上期指标
            formatted_SQindicators = []
            if SQindicators:
                formatted_SQindicators = [
                    f"{indicator} as {indicator_desc_map.get(indicator, indicator)}_上期"
                    for indicator in SQindicators
                ]
            #格式化每个维度
            formatted_dimensions = []
            dimension_mapping = {}
            for dimension in dimensions:
                dimension_code = dimension.get('code', '')
                dimension_desc = dimension.get('desc', '') or dimension_code
                dimension_granularity = dimension.get('granularity', '')

                code_table_name = None
                try:
                    metadata_filter = {"dimension_code": dimension_code}
                    dimension_result = self.chroma.list_records(
                        collection_name='dimensions',
                        embedding_model=None,
                        page=1,
                        page_size=1,
                        search_type='metadata',
                        record_id=None,
                        query_text='',
                        metadata_filter=metadata_filter,
                        document_filter=None,
                        n_results_requested=1
                    )
                    print(f"查询维度结果: {dimension_result}")
                    if dimension_result.get('records'):
                        dimension_metadata = json.loads(dimension_result['records'][0].get('document', '{}'))
                        code_table_name = dimension_metadata.get('related_code_table')
                except Exception as e:
                    print(f"查询维度 {dimension_code} 关联码表失败: {e}")
                #处理关联码表
                if code_table_name is None or (isinstance(code_table_name, float) and code_table_name != code_table_name):
                    print(f"警告: 维度 {dimension_code} 没有关联码表，使用默认拼接")
                    has_code_table = False# 没有关联码表
                else:
                    print(f"维度 {dimension_code} 关联码表: {code_table_name}")
                    has_code_table = True# 有关联码表
                # 根据是否有关联码表，决定拼接方式
                if not has_code_table:
                    formatted_dimension = f"{dimension_code} as {dimension_desc}"
                else:
                    if dimension_granularity:
                        formatted_dimension = f"{dimension_code}->{dimension_granularity} as {dimension_desc}"
                    else:
                        formatted_dimension = f"{dimension_code}->desc as {dimension_desc}"

                formatted_dimensions.append(formatted_dimension)
                dimension_mapping[dimension_code] = dimension_desc
                # 输出时修改维度字段名
                if dimension_granularity:
                    granularity_map = {
                        'YearAnaly': '年度',
                        'QuarterAnaly': '季度',
                        'MonthAnaly': '月度'
                    }
                    granularity_cn = granularity_map.get(dimension_granularity, dimension_granularity)
                    new_dim_key = f"{dimension_desc}_{granularity_cn}"
                    dimension_mapping[dimension_desc] = new_dim_key

            print(f"转换后参数: indicators={formatted_indicators}, dimensions={formatted_dimensions}, startDate={startDate}, endDate={endDate}")
            # **生成查询参数**
            query_params = {
                'kpis': ', '.join(formatted_indicators),
                'startDate': startDate,
                'endDate': endDate,
                'dims': ', '.join(formatted_dimensions) if formatted_dimensions else ''
            }
            # 如果有同期指标（TQindicators），则加上 TQKpis 参数
            if formatted_TQindicators:
                query_params['TQKpis'] = ', '.join(formatted_TQindicators)
            # 如果有上期指标（SQindicators），则加上 SQKpis 参数
            if formatted_SQindicators:
                query_params['SQKpis'] = ', '.join(formatted_SQindicators)
            if filter_expr:
                query_params['filter'] = filter_expr

            if permission_check and user_id:
                authorized_dimensions, dim_filters, err_msg = get_authorized_dimensions_and_filters(user_id)
                if not err_msg and dim_filters:
                    # 构建 dim_filters 的表达式
                    dim_filter_exprs = []
                    for f in dim_filters:
                        field = f.get('field')
                        operator = f.get('operator')
                        value = f.get('value')
                        if not field or not operator or value is None:
                            continue
                        # 支持 value 为逗号分隔字符串或列表
                        if isinstance(value, str):
                            value_list = [v.strip() for v in value.split(',') if v.strip()]
                        elif isinstance(value, list):
                            value_list = value
                        else:
                            value_list = [str(value)]
                        value_str = ','.join(value_list)
                        # 只支持 in 或 =，都转为 field = (v1,v2)
                        dim_filter_exprs.append(f"{field}->Desc=({value_str})")
                    # 合并原有 filter_expr 和 dim_filters
                    if dim_filter_exprs:
                        if 'filter' in query_params and query_params['filter']:
                            query_params['filter'] = f"{query_params['filter']} && {' && '.join(dim_filter_exprs)}"
                        else:
                            query_params['filter'] = ' && '.join(dim_filter_exprs)
            
            print("查询参数：", query_params)

            # **执行查询**
            query_result = self.indicator_service.query_indicator_data(query_params)
            print(f"查询结果类型: {type(query_result)}，内容 : {query_result}")
            print(f"indicator: {indicators}, indicator_desc: {indicator_desc}")

            if query_result is None or query_result.empty:
                return jsonify({'status': 'error', 'message': '查询结果为空', 'data': [], 'query_params': query_params}), 400
            if hasattr(query_result, 'to_dict'):
                query_result = query_result.to_dict(orient='records')

            for row in query_result:
                for dimension_desc, new_dim_key in dimension_mapping.items():
                    if dimension_desc in row:
                        row[new_dim_key] = row.pop(dimension_desc)

            result = []
            for row in query_result:
                new_row = {}
                for key, value in row.items():
                    new_row[key] = value
                # 优先保留带后缀的字段 (如 '_同期' 和 '_上期')
                if TQindicators:
                    for key, value in row.items():
                        # 处理带 '_同期' 后缀的字段
                        if '_同期' in key:
                            new_row[key] = value
                # 处理带 '_上期' 后缀的字段
                if SQindicators:
                    for key, value in row.items():
                        if '_上期' in key:
                            new_row[key] = value
                for dimension in dimensions:
                    dimension_code = dimension.get('code', '')
                    dimension_desc = dimension.get('desc') or dimension_code
                    if dimension_desc in row:
                        new_row[dimension_desc] = row[dimension_desc]
                result.append(new_row)

            return jsonify({
                'result': result,
                'status': 'success',
                'message': "",
                'query_params': query_params
            })
        except Exception as e:
            print(f"查询失败: {e}")
            return jsonify({
                'status': 'error',
                'message': str(e),
                'data': [],
                'query_params': query_params
            }), 500

    def find_dimension_values(self,data):
        print("Received request for query_dimension_values")

        dimension = data.get('dimension')
        dimension_desc = data.get('dimension_desc', '')
        key = data.get('key', '')
        limit = data.get('limit')

        if limit is None:
            limit = 10
        else:
            try:
                limit = int(limit)
            except ValueError:
                limit = 10
        print(f"Limit: {limit}")

        if not dimension:
            return {'status': 'error', 'message': '缺少维度编码'}, 400

        chroma = ChromaService()

        print(f"查询维度编码: {dimension}")
        metadata_filter = {
            "dimension_code": dimension
        }

        dimension_result = chroma.list_records(
            collection_name='dimensions',
            embedding_model=None,
            page=1,
            page_size=1,
            search_type='metadata',
            record_id=None,
            query_text='',
            metadata_filter=metadata_filter,
            document_filter=None,
            n_results_requested=1
        )
        print(f"查询结果: {dimension_result}")

        if not dimension_result.get('records'):
            print(f"未找到维度编码为 {dimension} 的维度信息")
            return {'status': 'error', 'message': f'未找到维度编码为 {dimension} 的维度信息'}, 404

        dimension_metadata = dimension_result['records'][0].get('document', '{}')
        dimension_metadata = json.loads(dimension_metadata)
        code_table_name = dimension_metadata.get('related_code_table')
        print(f"关联码表: {code_table_name}")

        if not code_table_name:
            print(f"维度 {dimension} 未配置对应的码表名称")
            return {'status': 'error', 'message': f'维度 {dimension} 未配置对应的码表名称'}, 400
        
        #如果是 CT_MDRDictionary，直接返回空
        if code_table_name == "CT_MDRDictionary":
            print(f"维度 {dimension} 关联码表为 CT_MDRDictionary，返回空列表")
            return {'status': 'success', 'data': []}, 200
        
        print(f"查询码表集合: code_tables")
        print(f"维度编码: {dimension} → 码表名: {code_table_name}")

        # connection = pymysql.connect(
        #     host="localhost",
        #     user="root",
        #     password="123456",
        #     database="dmp_bi2",
        #     charset="utf8mb4",
        #     cursorclass=pymysql.cursors.DictCursor
        # )
        connection = DatabaseService.get_connection(self.meta_db_config)

        query = f"""
        SELECT t.code, t.name, tt.dict_code, tt.dict_name
        FROM sm_dmp.ds_dictionary_items t
        LEFT JOIN sm_dmp.ds_dictionary tt ON t.dictionary_id = tt.id
        WHERE tt.dict_code = %s AND tt.dict_name LIKE %s
        LIMIT %s
        """
        print(f"Generated query: {query}")

        like_pattern = f"%{key}%"

        try:
            with connection.cursor() as cursor:
                cursor.execute(query, (code_table_name, like_pattern, limit))
                rows = cursor.fetchall()

                data_list = []
                for row in rows:
                    data_list.append({
                        "code": row["code"],
                        "desc": row["name"]
                    })

                print("Query result:", data_list)
        finally:
            connection.close()

        return {'status': 'success', 'data': data_list}, 200

    def find_indicators_values(self, indicator_code):

        metadata_filter = {
            "metric_code": indicator_code
        }
        print(f"使用的 metadata_filter: {metadata_filter}")
        indicator_result = self.chroma.list_records(
            collection_name='indicators',
            embedding_model=None,
            page=1,
            page_size=1,
            search_type='metadata',
            record_id=None,
            query_text='',
            metadata_filter=metadata_filter,
            document_filter=None,
            n_results_requested=1
        )
        print(indicator_result)

        if not indicator_result['records']:
            print("未找到该指标对应的描述")
            return []

        indicator_desc = json.loads(indicator_result['records'][0]['document']).get('metric_description', '')
        subject_area = indicator_result['records'][0].get('metadata', {}).get('subject_area', '')
        if not indicator_desc:
            print("未找到有效的指标描述")
            return []
        print(f"指标描述: {indicator_desc}")

        prefix = indicator_desc.split('_')[0]
        print(f"用于 document_filter 的前缀: {prefix}")

        filtered_docs = self.chroma.list_records(
            collection_name='indicators',
            embedding_model=None,
            page=1,
            page_size=100,
            search_type='hybrid',
            record_id=None,
            query_text=prefix,
            metadata_filter={"subject_area": subject_area},
            document_filter=prefix,
            n_results_requested=100
        )

        result_data = []
        for record in filtered_docs.get('records', []):
            doc = json.loads(record['document'])
            detailed_explanation = doc.get('detailed explanation', '')
            meaning_text = detailed_explanation if detailed_explanation else '无指标含义'

            result_data.append({
                "code": doc.get('metric_code', '无描述'),
                "desc": doc.get('metric_description', '无描述'),
                "unit": doc.get('unit', '无单位'),
                "meaning": meaning_text
            })

        return result_data
